<?php
/*
 * Copyright 2008 by Oliver Radwan, Maxwell Palmer, Nolan McNair,
* Taylor Talmage, and Allen Tucker.  This program is part of RMH Homebase.
* RMH Homebase is free software.  It comes with absolutely no warranty.
* You can redistribute it and/or modify it under the terms of the GNU
* General Public License as published by the Free Software Foundation
* (see <http://www.gnu.org/licenses/ for more information).
*/

/**
 * Functions to create, update, and retrieve information from the
 * dbShifts table in the database.  This table is used with the Shift
 * class.  Shifts are generated using the master schedule (through the
 * addWeek.php form), and retrieved by the calendar form and editShift.
 * @version May 1, 2008
 * @author Maxwell Palmer
 */

include_once('Shift.php');
include_once('dbPersons.php');
include_once('dbDates.php');
include_once('dbSCL.php');
include_once('dbinfo.php');

/**
 * Drops the dbShifts table if it exists, and creates a new one
 * Table fields:
 * id: mm-dd-yy-ss-ee or mm-dd-yy-ss-eeFam or mm-dd-yy-chef
 * start_time: integer hour equivalent to ss (0 ... 23)
 * end_time: integer hour equivalent to ee (0 ... 23)
 * venue: "" for House and "Fam" for family room
 * vacancies: # of vacancies for this shift
 * persons: list of people ids, followed by their name, ie "max1234567890+Max+Palmer"
 * sub_call_list: yes/no if shift has SCL
 * notes: shift notes
*/
function setup_dbShifts() {
	connect();
	mysql_query("DROP TABLE IF EXISTS dbShifts");
	$result=mysql_query("CREATE TABLE dbShifts (id CHAR(17) NOT NULL, " .
			"start_time INT, end_time INT, venue TEXT, vacancies INT, " .
			"persons TEXT, sub_call_list TEXT, notes TEXT, PRIMARY KEY (id))");
	if(!$result) {
		echo mysql_error();
		return false;
	}
	mysql_close();
	return true;
}

/**
 * Inserts a shift into the db
 * @param $s the shift to insert
 */
function insert_dbShifts($s) {
	if (! $s instanceof Shift) {
		die ("Invalid argument for insert_dbShifts function call".$s);
	}
	connect();
	$query = 'SELECT * FROM dbShifts WHERE id ="'.$s->get_id().'"';
	$result = mysql_query ($query);
	if (mysql_num_rows($result)!=0) {
		delete_dbShifts($s);
		connect();
	}
	$query="INSERT INTO dbShifts VALUES (\"".$s->get_id()."\",\"".
			$s->get_start_time()."\",\"".$s->get_end_time()."\",\"".$s->get_venue()."\",".
			$s->num_vacancies().",\"".
			implode("*",$s->get_persons())."\",\"".$s->get_sub_call_list()."\",\"".$s->get_notes()."\")";
	$result=mysql_query($query);
	if(!$result) {
		echo "unable to insert into dbShifts ".$s->get_id().mysql_error();
		mysql_close();
		return false;
	}
	mysql_close();
	return true;
}

/**
 * Deletes a shift from the db
 * @param $s the shift to delete
 */
function delete_dbShifts($s) {
	if (! $s instanceof Shift)
		die ("Invalid argument for delete_dbShifts function call");
	connect();
	$query="DELETE FROM dbShifts WHERE id=\"".$s->get_id()."\"";
	$result=mysql_query($query);
	if(!$result) {
		echo "unable to delete from dbShifts ".$s->get_id().mysql_error();
		mysql_close();
		return false;
	}
	mysql_close();
	return true;
}

/**
 * Updates a shift in the db by deleting it (if it exists) and then replacing it
 * @param $s the shift to update
 */
function update_dbShifts($s) {
	if (! $s instanceof Shift)
		die ("Invalid argument for dbShifts->replace_shift function call");
	delete_dbShifts($s);
	insert_dbShifts($s);
	return true;
}

/**
 * Selects a shift from the database
 * @param $id a shift id
 * @return Shift or null
 */
function select_dbShifts($id) {
	connect();
	$s=null;
	$query = "SELECT * FROM dbShifts WHERE id =\"".$id."\"";
	$result = mysql_query ($query);
	if (!$result) {
		echo 'Could not run query2: ' . mysql_error();
	}
	else {
		$result_row=mysql_fetch_row($result);
		if($result_row) {
			$s=new Shift($result_row[0], $result_row[4],
					explode("*",$result_row[5]), $result_row[6],$result_row[7]);
		}
	}
	mysql_close();
	return $s;
}
/**
 * Selects all shifts from the database for a given date and venue
 * @param $id is a shift id
 * @return a result set or false (if there are no shifts for that date and venue)
 */
function selectDateVenue_dbShifts($date, $venue) {
	connect();
	$query = "SELECT * FROM dbShifts WHERE id LIKE '%".$date."%' AND venue LIKE '%".$venue."%'";
	$result = mysql_query($query);
	mysql_close();
	return $result;
}
/**
 * Returns an array of $ids for all shifts scheduled for the person having $person_id
 */
function selectScheduled_dbShifts($person_id) {
	connect();
	$shift_ids = mysql_query("SELECT id FROM dbShifts WHERE persons LIKE '%".$person_id."%' ORDER BY id");
	$shifts=array();
	if($shift_ids){
		while($thisRow = mysql_fetch_array($shift_ids, MYSQL_ASSOC)){
			$shifts[] = $thisRow['id'];
		}
	}
	mysql_close();
	return $shifts;
}
/**
 * Returns the month, day, year, start, or end time of a shift from its id
 */
function get_shift_month($id){
	return substr($id,0,2);
}
function get_shift_day($id){
	return substr($id,3,2);
}
function get_shift_year($id){
	return substr($id,6,2);
}
function get_shift_start($id){
	if (substr($id, 11, 1) == "-")
		return substr($id,9,2);
	else return substr($id,9,1);
}
function get_shift_end($id){
	if (substr($id,11,1)=="-")
		return substr($id,12,2);
	else return substr($id,11,2);
}
/*
 * Creates the $shift_name of a shift, e.g. "Sunday, February 14, 2010 from 2pm to 5pm"
*         from its $id, e.g. "02-14-10-14-17", $start_time, and $end_time.
*/
function get_shift_name_from_id($id) {
	$shift_name=date("l, F jS, Y",
			mktime(0,0,0,get_shift_month($id),get_shift_day($id),get_shift_year($id)));
	$special_cases = array ("night" => " night shift", "chef" => " guest chef");
	if (array_key_exists(substr($id, 9), $special_cases))
		$shift_name = $shift_name . $special_cases[substr($id, 9)];
	else {
		if (strpos($id, "Fam") !== false)
			$shift_name = $shift_name . " Family Room ";
		$shift_name = $shift_name . " from ";
		$st = get_shift_start($id);
		$et = get_shift_end($id);
		$st = $st <12 ? $st . "am" : $st - 12 . "pm";
		if ($st == "0pm")
			$st = "12pm";
		$et = $et <12 ? $et . "am" : $et - 12 . "pm";
		if ($et == "0pm")
			$et = "12pm";
		$shift_name = $shift_name . $st . " to " . $et;
	}
	return $shift_name;
}

/**
 * Tries to move a shift to a new start and end time.  New times must
 * not overlap with any other shift on the same date and venue
 * @return false if shift doesn't exist or there's an overlap
 * Otherwise, change the shift in the database and @return true
 */
function move_shift ($s, $new_start, $new_end) {
	// first, see if it exists
	$old_s = select_dbShifts($s->get_id());
	if ($old_s == null)
		return false;
	// now see if it can be moved by looking at all other shifts for the same date and venue
	$new_s = $s->set_start_end_time($new_start, $new_end);
	$current_shifts = selectDateVenue_dbShifts($s->get_date(), $s->get_venue());
	connect();
	for($i=0;$i<mysql_num_rows($current_shifts);++$i) {
		$same_day_shift = mysql_fetch_row($current_shifts);
		if ($old_s->get_id() == $same_day_shift[0])  // skip its own entry
			continue;
		if (timeslots_overlap($same_day_shift[1],$same_day_shift[2],
				$new_s->get_start_time(), $new_s->get_end_time())) {
			$s = $old_s;
			mysql_close();
			return false;
		}
	}
	mysql_close();
	// we're good to go
	replace_dbDates($old_s, $new_s);
	delete_dbShifts($old_s);
	return true;
}
/**
 * @result == true if $s1's timeslot overlaps $s2's timeslot, and false otherwise.
 */
function timeslots_overlap($s1_start, $s1_end, $s2_start, $s2_end) {
	if ($s1_end > $s2_start) {
		if ($s1_start >= $s2_end)
			return false;
		else return true;
	}
	else return false;
}

//total number of vancancies
function total_number_vacancies()
{
	connect();
	$query = 'SELECT vacancies FROM Shift';
	$result = mysql_query ($query);
	if (!$result) {
		echo 'Could not run query: ' . mysql_error();}
	mysql_close();
	return $result;
	
}

//average number of vacancies per day
function total_average_vacancies()
{
	$result = total_number_vacancies()/get_shift_day($id);
	return result;

}



//number shift work by volunteer
function total_shift_Volunteer()
{
	connect();
	$query = 'SELECT Shift FROM dbShift WHERE type = volunteer';
	$result = mysql_query ($query);
	if (!$result) {
		echo 'Could not run query: ' . mysql_error();}
		mysql_close();
		return $result;
}

//average number of shift work by voluntter
function average_shift_per_volunteer()
{
	$result = total_shift_Volunteer()/get_alltypes('volunteer');
	return result;
}
?>
